package com.jcgyl.export.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

import org.apache.log4j.Logger;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

import jxl.Cell;
import jxl.Workbook;

public class POIExcelHelper {
	public static Logger logger = Logger.getLogger(POIExcelHelper.class);

	/**
	 * 设置表头样式
	 * 
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle getTitleStyle(HSSFWorkbook workbook) {
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.BLUE.index);
		font.setFontHeight((short) 200);
		font.setFontName("楷体_GB2312");
		font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);

		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
		style.setFont(font);
		style.setFillForegroundColor(HSSFColor.GREY_50_PERCENT.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		return style;
	}

	/**
	 * 设置表格特别数据样式
	 * 
	 * @param workbook
	 * @return
	 */
	public static HSSFCellStyle getDataStyle2(HSSFWorkbook workbook) {
		HSSFFont font = workbook.createFont();
		font.setColor(HSSFColor.BLACK.index);
		font.setFontHeight((short) 200);
		font.setFontName("楷体_GB2312");

		HSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(HSSFCellStyle.VERTICAL_CENTER);
		style.setAlignment(HSSFCellStyle.ALIGN_LEFT);
		style.setFont(font);
		style.setFillForegroundColor(HSSFColor.GREY_40_PERCENT.index);
		style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
		return style;
	}

	/**
	 * 创建单元格内容
	 * 
	 * @param row
	 * @param id
	 * @param value
	 * @param style
	 */
	@SuppressWarnings("deprecation")
	public static void createCell(HSSFRow row, int id, String value, HSSFCellStyle style) {
		HSSFCell cell = row.createCell((short) id);
		cell.setCellType(HSSFCell.CELL_TYPE_STRING);
		cell.setCellValue(value);
		if (style != null) {
			cell.setCellStyle(style);
		}
	}

	/**
	 * 创建报表文件
	 * 
	 * @param workbook
	 * @param dir
	 * @param filename
	 * @throws IOException
	 */
	public static void createFile(HSSFWorkbook workbook, String dir, String filename) throws IOException {
		dir = dir == null ? "" : dir.trim();
		if (!"".equals(dir)) {
			if (!dir.endsWith(File.separator)) {
				dir += File.separator;
			}
		}
		logger.debug("out put dir: " + dir);
		File outdir = new File(dir);
		if (!outdir.exists()) {
			outdir.mkdirs();
		}
		FileOutputStream fOut = new FileOutputStream(dir + filename);
		workbook.write(fOut);
		fOut.flush();
		fOut.close();
		logger.info(dir + filename + "已经生成!");
	}

	/**
	 * 读取Excel中所有的列
	 * 
	 * @param filename
	 * @return
	 * @throws IOException
	 */
	public static List<Cell[]> jxlGetExcelColumns(String filename) throws IOException {
		InputStream is = null;
		jxl.Workbook rwb = null;
		List<Cell[]> list = new ArrayList<Cell[]>();
		try {
			is = new FileInputStream(filename);
			rwb = Workbook.getWorkbook(is);
			// Sheet[] sheets = rwb.getSheets();
			// int sheetLen = sheets.length;
			jxl.Sheet rs = rwb.getSheet(0); // 读取第一个工作表的数据

			// getRows() 获取总共多少列...getColumn(n)获取第n列...
			for (int i = 0; i < rs.getColumns(); i++) {
				list.add(rs.getColumn(i));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			rwb.close();
			is.close();
		}
		return list;
	}

	/**
	 * 读取Excel中所有的行
	 * 
	 * @param filename
	 * @return
	 */
	public static List<Cell[]> jxlGetExcelRows(String filename, boolean firstRowHeader) {
		InputStream is = null;
		jxl.Workbook rwb = null;
		List<Cell[]> list = new ArrayList<Cell[]>();
		try {
			is = new FileInputStream(filename);
			rwb = Workbook.getWorkbook(is);
			jxl.Sheet rs = rwb.getSheet(0); // 默认先读取第一个工作表的数据
			int i = 0;
			if (firstRowHeader) {
				i = 1;
			}
			for (; i < rs.getRows(); i++) {
				list.add(rs.getRow(i));
			}
		} catch (Exception ex) {
			ex.printStackTrace();
		} finally {
			rwb.close();
			try {
				is.close();
			} catch (IOException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 获得单元格字符串
	 * 
	 * @throws UnSupportedCellTypeException
	 */
	public static String getStringCellValue(HSSFCell cell) {
		if (cell == null) {
			return null;
		}

		String result = "";
		switch (cell.getCellType()) {
		case HSSFCell.CELL_TYPE_BOOLEAN:
			result = String.valueOf(cell.getBooleanCellValue());
			break;
		case HSSFCell.CELL_TYPE_NUMERIC:
			if (HSSFDateUtil.isCellDateFormatted(cell)) {
				java.text.SimpleDateFormat TIME_FORMATTER = new java.text.SimpleDateFormat("yyyy-MM-dd");
				result = TIME_FORMATTER.format(cell.getDateCellValue());
			} else {
				double doubleValue = cell.getNumericCellValue();
				result = "" + doubleValue;
			}
			break;
		case HSSFCell.CELL_TYPE_STRING:
			if (cell.getRichStringCellValue() == null) {
				result = null;
			} else {
				result = cell.getRichStringCellValue().getString();
			}
			break;
		case HSSFCell.CELL_TYPE_BLANK:
			result = null;
			break;
		case HSSFCell.CELL_TYPE_FORMULA:
			try {
				result = String.valueOf(cell.getNumericCellValue());
			} catch (Exception e) {
				result = cell.getRichStringCellValue().getString();
			}
			break;
		default:
			result = "";
		}

		return result;
	}

}